package com.husky.easyexcle.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.husky.easyexcle.Constants.Constants;
import com.husky.easyexcle.entity.*;
import com.husky.easyexcle.listener.LunchDataListenerMultiSheetForCSDN;
import com.husky.easyexcle.service.ExcelService;
import com.husky.easyexcle.utils.ExcelUtils;
import com.husky.easyexcle.utils.ExcleUtil;
import com.husky.easyexcle.utils.ListUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.system.ApplicationHome;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;


/**
 * @Author husky
 * @Date 2022/3/22 16:09
 * @Description: Excelweb相关业务实现类
 **/
@Slf4j
@Service
public class ExcelSerivceImpl implements ExcelService {


    private static final Logger logger = LoggerFactory.getLogger(ExcelSerivceImpl.class);
    /**
     * 读取上传Excel数据
     * @param file
     * @return
     */
    @Override
    public List<ExcelDateEntity> readExcelData(MultipartFile file) {
        return ExcleUtil.readWebExcelData(file, ExcelDateEntity.class);
    }


    /**
     * 读取dependent依赖
     * @param file
     * @return
     */
    @Override
    public void readExcelDepData(MultipartFile file) {

        List<EtlJobDepEntity> etlJobDepEntities = ExcleUtil.readWebExcelData(file, EtlJobDepEntity.class);


        List<TaskDependentEntity> list = new CopyOnWriteArrayList<>();
        for (int i = 0; i < etlJobDepEntities.size(); i++) {
            TaskDependentEntity taskDependentEntity = new TaskDependentEntity();
            taskDependentEntity.setProceeProjectCode(etlJobDepEntities.get(i).getETL_System());
            taskDependentEntity.setTaskName(etlJobDepEntities.get(i).getETL_Job().replaceAll("\\.","_"));
            taskDependentEntity.setTaskType(Constants.TASKTYPE_DEPENDENT);
            taskDependentEntity.setDepProjectCode(etlJobDepEntities.get(i).getDependency_System());
            taskDependentEntity.setDependentTask(etlJobDepEntities.get(i).getDependency_Job().replaceAll("\\.","_"));
            taskDependentEntity.setDepCycle(Constants.DEPCYCLE);
            taskDependentEntity.setDetaValue(Constants.DATAVALUE);
            //todo处理依赖业务逻辑
            list.add(taskDependentEntity);
        }
        ExcleUtil.exportExcle("作业依赖.xlsx", "BDS_作业依赖",0, list, TaskDependentEntity.class);
    }


    /**
     * 加工任务Excel数据
     * @param file
     * @return
     */
    @Override
    public void readExcelJobData(MultipartFile file) {

        List<EtlJobEntity> etlJobEntities = ExcleUtil.readWebExcelDepData(file, EtlJobEntity.class);

        List<TaskDefinitionEntity> list = new CopyOnWriteArrayList<>();
        Map<String,Map<String,String>> processNameMap = new HashMap<>();
        for (int i = 0; i < etlJobEntities.size(); i++) {
            String processName = "";
            String etlSystem = etlJobEntities.get(i).getETL_System();
            List<String> paramList = new ArrayList<>();

            switch (etlSystem){
                case "G07" :
                    processName = Constants.PROCESS_G07;
                    break;
                case "G08" :
                    processName = Constants.PROCESS_G08;
                    break;
                case "C01" :
                    processName = "";
                    break;
                default:
                    continue;

            }
            //执行脚本
            String rawScript = "";
            //自定义参数
            String localParams= "";
            try {
                paramList = readExcel(etlSystem);
                rawScript = paramList.get(0);
                localParams = paramList.get(1).replace("{1}",etlJobEntities.get(i).getETL_JOB().toLowerCase());;
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
            String FinEtlJob = "";
            //调度时间
            String jobStartTime = "";
            //作业流名称拼接
            if(etlJobEntities.get(i).getETL_System().equals("C01")){
                String substring = etlJobEntities.get(i).getETL_JOB().substring(0,3);
                String jobType = etlJobEntities.get(i).getJobType();
                switch (substring){
                    case "CHE":
                        processName = etlJobEntities.get(i).getETL_JOB();
                        if(jobType.equals("D")){
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(11,etlJobEntities.get(i).getJob_start_time().length());
                        }else if (jobType.equals("M")){
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(8,etlJobEntities.get(i).getJob_start_time().length());
                        }
                        break;
                    case "FTP":
                        processName = "FTP_NXY";
                        if(jobType.equals("D")){
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(11,etlJobEntities.get(i).getJob_start_time().length());
                        }else if (jobType.equals("M")){
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(8,etlJobEntities.get(i).getJob_start_time().length());
                        }
                        break;
                    case "OGG":
                        String[] job = etlJobEntities.get(i).getETL_JOB().split("_");
                        String etl_Job =  job[0]+"_"+job[1]+"_"+ job[2]+"_"+job[3];
                        //作业启动方式（M月,D天）
                        Map<String, String> taskNameMap = processNameMap.get(etl_Job);
                        int countInt = 1;
                        if (Objects.isNull(taskNameMap)){
                            String count = "1";
                            taskNameMap = new HashMap<>();
                            taskNameMap.put(etl_Job,count);
                        }else {
                            countInt = Integer.valueOf(taskNameMap.get(etl_Job))+1 ;
                        }
                        if (jobType.equals("D")){
                            //作业启动时间
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(11,etlJobEntities.get(i).getJob_start_time().length());
                            FinEtlJob = taskNameMap.get(jobStartTime);
                            if (FinEtlJob==null){
                                String count = String.valueOf(countInt);
                                if (count.length()<2){
                                    count = "0"+count;
                                    taskNameMap.put(etl_Job,count);
                                }else {
                                    taskNameMap.put(etl_Job,count);
                                }
                                FinEtlJob = etl_Job+"_"+count;
                                taskNameMap.put(jobStartTime,FinEtlJob);
                                processNameMap.put(etl_Job,taskNameMap);
                            }
                        }else if (jobType.equals("M")){
                            //作业启动时间
                            jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(8,etlJobEntities.get(i).getJob_start_time().length());
                            FinEtlJob = taskNameMap.get(jobStartTime);
                            if (FinEtlJob==null){
                                String count = String.valueOf(countInt);
                                if (count.length()<2){
                                    count = "0"+count;
                                    taskNameMap.put(etl_Job,count);
                                }else {
                                    taskNameMap.put(etl_Job,count);
                                }
                                FinEtlJob = etl_Job+"_"+count;
                                taskNameMap.put(jobStartTime,FinEtlJob);
                                processNameMap.put(etl_Job,taskNameMap);
                            }
                        }
                        break;
                }
            }else {
                String[] job = etlJobEntities.get(i).getETL_JOB().split("_");
                String etl_Job = job[3];
                //作业启动方式（M月,D天）
                String jobType = etlJobEntities.get(i).getJobType();
                Map<String, String> taskNameMap = processNameMap.get(etl_Job);
                int countInt = 1;
                if (Objects.isNull(taskNameMap)){
                    String count = "1";
                    taskNameMap = new HashMap<>();
                    taskNameMap.put(etl_Job,count);
                }else {
                    countInt = Integer.valueOf(taskNameMap.get(etl_Job))+1 ;
                }
                if (jobType.equals("D")){
                    //作业启动时间
                    jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(11,etlJobEntities.get(i).getJob_start_time().length());
                    FinEtlJob = taskNameMap.get(jobStartTime);
                    if (FinEtlJob==null){
                        String count = String.valueOf(countInt);
                        if (count.length()<2){
                            count = "0"+count;
                            taskNameMap.put(etl_Job,count);
                        }else {
                            taskNameMap.put(etl_Job,count);
                        }
                        FinEtlJob = etl_Job+"_"+count;
                        taskNameMap.put(jobStartTime,FinEtlJob);
                        processNameMap.put(etl_Job,taskNameMap);

                    }
                }else if (jobType.equals("M")){
                    //作业启动时间
                    jobStartTime = etlJobEntities.get(i).getJob_start_time().substring(8,etlJobEntities.get(i).getJob_start_time().length());
                    FinEtlJob = taskNameMap.get(jobStartTime);
                    if (FinEtlJob==null){
                        String count = String.valueOf(countInt);
                        if (count.length()<2){
                            count = "0"+count;
                            taskNameMap.put(etl_Job,count);
                        }else {
                            taskNameMap.put(etl_Job,count);
                        }
                        FinEtlJob = etl_Job+"_"+count;
                        taskNameMap.put(jobStartTime,FinEtlJob);
                        processNameMap.put(etl_Job,taskNameMap);
                    }
                }
            }
            jobStartTime = convertSysCron(jobStartTime);
            //获取调度开始时间和结束时间
            TaskDefinitionEntity taskDefinitionEntity = new TaskDefinitionEntity();
            String startTime = etlJobEntities.get(i).getJob_start_time();
            String endTime = etlJobEntities.get(i).getJob_stop_time();
            if (startTime!=null&&endTime!=null){
                taskDefinitionEntity.setSchedulerEnable(Constants.SCHEDULERENABLE);
                taskDefinitionEntity.setSchedulerStartTime(startTime);
                taskDefinitionEntity.setSchedulerEndTime(endTime);
            }else {
                taskDefinitionEntity.setSchedulerEnable("否");
            }
            taskDefinitionEntity.setNumberId(etlJobEntities.get(i).getETL_System());
            taskDefinitionEntity.setProjectCode("pashujasdasd");
            taskDefinitionEntity.setName(etlJobEntities.get(i).getETL_JOB());
            taskDefinitionEntity.setZhName(etlJobEntities.get(i).getETL_JOB().toLowerCase());
            taskDefinitionEntity.setProcessName(processName+FinEtlJob);
            taskDefinitionEntity.setTaskPriority(Constants.TASKPRIORITY);
            taskDefinitionEntity.setSchedulerTime(jobStartTime);
            taskDefinitionEntity.setTaskGroupPriority(Constants.TASKGROUPPRIORITY);
            taskDefinitionEntity.setWorkerGroupName(Constants.WORKERGROUPNAME);
            taskDefinitionEntity.setRetryTimes(Constants.TASKGROUPPRIORITY);
            taskDefinitionEntity.setRetryInterval(Constants.TASKGROUPPRIORITY);
            taskDefinitionEntity.setTaskType(Constants.TASKTYPE);
            taskDefinitionEntity.setRawScript(rawScript);
            taskDefinitionEntity.setLocalParams(localParams);
            //运行脚本
            if (etlSystem.equals("G07") || etlSystem.equals("G08")){
                taskDefinitionEntity.setResourceList(
                        etlJobEntities.get(i).getETL_JOB().toLowerCase()+Constants.SQL_SCRIPT+Constants.GAUSSDB_SCRIPT+Constants.GAUSSDB_ENV);
            }else {
                taskDefinitionEntity.setResourceList(etlJobEntities.get(i).getETL_JOB().toLowerCase()+Constants.SQL_SCRIPT);
            }
            taskDefinitionEntity.setTimeoutAlarm(Constants.TIMEOUTALARM);
            taskDefinitionEntity.setRetryInterval(Constants.TASKGROUPPRIORITY);
            list.add(taskDefinitionEntity);
        }

        ExcleUtil.exportExcle("作业流_作业.xlsx", "BDS_作业配置",0, list, TaskDefinitionEntity.class);
    }



    /*
     *
     * 处理env资源文件关系
     *
     * */
    @Override
    public void readEnvFile(MultipartFile file) {
        List<TaskDefinitionEntity> taskDefinition = ExcleUtil.readWebExcelHeadRow(file, TaskDefinitionEntity.class,3);
        List<EnvfileEntity> envfileEntityList = ExcleUtil.readWebExcelSheetData(file, EnvfileEntity.class,1);
        List<TaskDefinitionEntity> taskDefinitionEntities = ListUtil.compareNewEnvFile(taskDefinition, envfileEntityList);

        ExcleUtil.exportExcle("env作业流_作业.xlsx", "BDS_作业配置", 0,taskDefinitionEntities, TaskDefinitionEntity.class);
    }


    /*
     *
     * 处理G04临时表依赖关系
     *
     * */
    @Override
    public void reaDepFile(MultipartFile file) {
        List<TaskDependentEntity> taskDependent = ExcleUtil.readWebExcelHeadRow(file, TaskDependentEntity.class,2);
        List<ProcessNameEntity> processName = ExcleUtil.readWebExcelSheetData(file, ProcessNameEntity.class,1);


        List<TaskDependentEntity> taskDefinitionEntities = ListUtil.compareDepData(taskDependent, processName);

        ExcleUtil.exportExcle("dep作业依赖.xlsx", "BDS_作业配置", 0,taskDefinitionEntities, TaskDependentEntity.class);
    }



    /*
    *
    * 处理作业流_作业依赖关系
    * G04->G01
    * G07->G04
    * G08->G07
    *
    * */
    @Override
    public void taskDep(MultipartFile file) {
        List<TaskDefinitionEntity> taskDependent = ExcleUtil.readWebExcelHeadRow(file, TaskDefinitionEntity.class,3);
        List<TaskDependentEntity> processName = ExcleUtil.readWebExcelSheetData(file, TaskDependentEntity.class,2);


        List<TaskDependentEntity> taskDefinitionEntities = ListUtil.compareNewTaskDepData(taskDependent, processName);

        ExcleUtil.exportExcle("作业流_作业依赖.xlsx", "BDS_作业配置", 0,taskDefinitionEntities, TaskDependentEntity.class);
    }

    @Override
    public void readExcelVatInvoiceData(MultipartFile file) {
        List<VatInvoiceDTO> vatInvoiceDTOList = ExcleUtil.readWebExcelData(file, VatInvoiceDTO.class);

        List<VatInvoiceDTO> list = new CopyOnWriteArrayList<>();
        for (int i = 0; i < vatInvoiceDTOList.size(); i++) {
            VatInvoiceDTO vatInvoiceDTO = new VatInvoiceDTO();
            vatInvoiceDTO.setInvoiceDate(vatInvoiceDTOList.get(i).getInvoiceDate());
            vatInvoiceDTO.setInvoiceNo(vatInvoiceDTOList.get(i).getInvoiceNo());
            vatInvoiceDTO.setInvoiceCode(vatInvoiceDTOList.get(i).getInvoiceCode());
            vatInvoiceDTO.setTotalAmount(vatInvoiceDTOList.get(i).getTotalAmount());
            vatInvoiceDTO.setTotalNoTaxAmount(vatInvoiceDTOList.get(i).getTotalNoTaxAmount());
            vatInvoiceDTO.setResultDto(JSONUtil.toJsonStr(vatInvoiceDTOList.get(i)));
            //todo
            list.add(vatInvoiceDTO);
        }
        ExcleUtil.exportExcle("发票处理数据.xlsx", "sheet1",0, list, VatInvoiceDTO.class);
    }


    @Override
    public ResponseEntity<byte[]> readNewExcelVatInvoiceData(MultipartFile file) throws Exception {

        final String originalFilename = file.getOriginalFilename();
        final String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1).toLowerCase();
        String message = ExcelUtils.check(file, suffix);
        if (StringUtils.isNotEmpty(message)) {
            throw new Exception();
        }
        Workbook wb = null;
        try {
            final InputStream inputStream = file.getInputStream();
            if ("xlsx".equals(suffix)) {
                wb = new XSSFWorkbook(inputStream); //创建2007版本Excel工作簿对象
            } else if ("xls".equals(suffix)) {
                wb = new HSSFWorkbook(inputStream); //创建2003版本Excel工作簿对象
            }
        } catch (IOException e) {
            e.printStackTrace();
            logger.info("表格导入异常");
        }

        List<VatInvoiceDTO> vatInvoiceDTOList = new ArrayList<VatInvoiceDTO>();
        for (int j = 0; j < wb.getNumberOfSheets(); j++) {
            Sheet sheet = wb.getSheetAt(j); //获取第x个工作表
            final String sheetName = sheet.getSheetName();

            final int lastRowNum = sheet.getLastRowNum();
            logger.info("行{}", lastRowNum);
            for (int i = 1; i <= lastRowNum; i++) {//循环Excel文件的i=1行开始
                Row row = sheet.getRow(i);

                // 公共字段
                final String invoiceDate = ExcelUtils.getCellValue(row.getCell(0));//开票日期
                // 工作流名称为空直接忽略，空行直接忽略
                if (StringUtils.isEmpty(invoiceDate)) continue;
                final String invoiceNo = ExcelUtils.getCellValue(row.getCell(1));//发票号码
                final String invoiceCode = ExcelUtils.getCellValue(row.getCell(2));//发票代码
                final String projectName = ExcelUtils.getCellValue(row.getCell(3));//项目名称
                final String creditorName = ExcelUtils.getCellValue(row.getCell(4));//债权人
                final String obligorName = ExcelUtils.getCellValue(row.getCell(5));//债务人
                final Double totalAmount = Double.valueOf(ExcelUtils.getCellValue(row.getCell(6)));//发票金额(含税)
                final Double totalNoTaxAmount = Double.valueOf(ExcelUtils.getCellValue(row.getCell(7)));//发票金额(不含税)

                final VatInvoiceDTO vatInvoiceDTO = new VatInvoiceDTO();
                vatInvoiceDTO.setInvoiceDate(invoiceDate);
                vatInvoiceDTO.setInvoiceNo(invoiceNo);
                vatInvoiceDTO.setInvoiceCode(invoiceCode);
                vatInvoiceDTO.setTotalAmount(BigDecimal.valueOf(totalAmount));
                vatInvoiceDTO.setTotalNoTaxAmount(BigDecimal.valueOf(totalNoTaxAmount));
                vatInvoiceDTOList.add(vatInvoiceDTO);
            }
        }
        String fileName = "交易数据.xlsx";
        byte[] bytes = exportToExcel(vatInvoiceDTOList);
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", fileName);

        return ResponseEntity.ok()
                .headers(headers)
                .body(bytes);

    }


    private byte[] exportToExcel(List<VatInvoiceDTO> vatInvoiceDTOList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("交易数据");

        // 创建表头
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("发票号码");
        header.createCell(1).setCellValue("发票代码");
        header.createCell(2).setCellValue("发票金额(含税)");
        header.createCell(3).setCellValue("发票金额(不含税)");

        // 写入数据
        for (int i = 0; i < vatInvoiceDTOList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            VatInvoiceDTO invoiceDTO = vatInvoiceDTOList.get(i);
            row.createCell(0).setCellValue(invoiceDTO.getId());
            row.createCell(1).setCellValue(invoiceDTO.getInvoiceNo());
            row.createCell(2).setCellValue(invoiceDTO.getInvoiceCode());
            row.createCell(3).setCellValue(invoiceDTO.getTotalAmount().toString());
            row.createCell(4).setCellValue(invoiceDTO.getTotalNoTaxAmount().toString());
        }
        // 写入到字节数组
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return out.toByteArray();
    }





    //转换cron表达式
    private static String convertSysCron(String time){
        time=time.trim();
        String cron="* ? *";
        if(time==null||time.isEmpty()){
            return null;
        }
        if(time.length()==8){
            cron="* "+cron;
            String[] times=time.split(":");
            for(String key:times){
                int keyInt=Integer.valueOf(key);
                cron=keyInt+" "+cron;
            }
        }else if(time.length()>8){
            String[] days=time.split(" ");
            int day=Integer.valueOf(days[0]);
            String[] times=days[1].split(":");
            cron=day+" "+cron;
            for(String key:times){
                int keyInt=Integer.valueOf(key);
                cron=keyInt+" "+cron;
            }
        }
        return cron;
    }
    public List<String> readExcel(String etlSystem) throws Exception{
        ApplicationHome ah = new ApplicationHome();
        //excle 导出文件夹
        String excelPath = ah.getDir().getPath()+"/src/main/resources/excelTemelate/";
        String fileName = "";
        if(etlSystem.equals("C01")){
         fileName =excelPath +"日切任务.xlsx";
        }else {
         fileName =excelPath +"加工任务.xlsx";
        }
        ExcelReader excelReader =
                EasyExcel.read(fileName).build();
        // 每个sheet分别创建监听器
        LunchDataListenerMultiSheetForCSDN listener0 = new LunchDataListenerMultiSheetForCSDN();

        ReadSheet readSheet0 =
                EasyExcel.readSheet(0).registerReadListener(listener0).build();

        // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        // 可以传多个ReadSheet对象，读多个sheet
        excelReader.read(readSheet0);

        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
        // 程序执行到这里，excel 已经读完了，下面是处理数据

        // 示范一个读数据的方式，非重点，你可以用自己的方式
        List<List<String>> dataList = listener0.getDataList();
        if (dataList.size()>0){
        List<String> strings = dataList.get(0);
        return strings;
        }
        throw  new Exception("读取的excel内容异常，请检查！");
    }

}
